package cn.school.mybatis.test;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;

import cn.school.mybatis.dao.MyBatisUtil;
import cn.school.mybatis.dao.StudentDao;
import cn.school.mybatis.pojo.Student;

public class TestStudent {

	// 条件查询
	public void searchStu() {
		SqlSession session = MyBatisUtil.getSqlSession();
		StudentDao studentDao = session.getMapper(StudentDao.class);
		Map<String, Object> map = new HashMap<>();
		map.put("wechat", "w");
		map.put("qq", "6");
		map.put("sname", "李");

		List<Student> list = studentDao.searchStu(map);
		MyBatisUtil.close(session);
		for (Student s : list) {
			System.out.print(s);
		}
	}

	// 条件查询人数
	public void searchStuCount() {
		SqlSession session = MyBatisUtil.getSqlSession();
		StudentDao studentDao = session.getMapper(StudentDao.class);
		Map<String, Object> map = new HashMap<>();
		map.put("wechat", "w");
		map.put("qq", "6");
		map.put("sname", "李");

		int count = studentDao.searchStuCount(map);
		System.out.println("满足条件的共有" + count + "人");

		MyBatisUtil.close(session);

	}

	// 查询全部
	public void searchAll() {
		SqlSession session = MyBatisUtil.getSqlSession();
		StudentDao studentDao = session.getMapper(StudentDao.class);
		List<Student> list = studentDao.searchAll();
		MyBatisUtil.close(session);
		for (Student s : list) {
			System.out.print(s);
		}

	}

	// 查询数量
	public void searchCount() {
		SqlSession session = MyBatisUtil.getSqlSession();
		StudentDao studentDao = session.getMapper(StudentDao.class);
		int count = studentDao.searchCount();
		MyBatisUtil.close(session);
		System.out.println("共有" + count + "条记录");
	}

	// 查询单个
	public void getOne(int num) {
		SqlSession session = MyBatisUtil.getSqlSession();
		StudentDao studentDao = session.getMapper(StudentDao.class);
		Student result = studentDao.get(num);
		System.out.println(result);
		MyBatisUtil.close(session);
	}

	// 查询单个
	public void searchGrade(int num) {
		SqlSession session = MyBatisUtil.getSqlSession();
		StudentDao studentDao = session.getMapper(StudentDao.class);
		Student result = studentDao.searchGrade(num);
		System.out.println(result);
		MyBatisUtil.close(session);
	}

	// 分页查询
	public void searchLimit(int pageNo, int pageSize) {
		SqlSession session = MyBatisUtil.getSqlSession();
		StudentDao studentDao = session.getMapper(StudentDao.class);
		int offset = (pageNo - 1) * pageSize;
		offset = Math.max(offset, 0);
		List<Student> list = studentDao.searchLimit(offset, pageSize);
		MyBatisUtil.close(session);
		for (Student s : list) {
			System.out.print(s);
		}
	}

	// 按条件模糊分页查询
	public void fuzzySarch(int pageNo, int pageSize, String wechat, String qq, String sname) {

		SqlSession session = MyBatisUtil.getSqlSession();
		StudentDao studentDao = session.getMapper(StudentDao.class);
		int offset = (pageNo - 1) * pageSize;
		offset = Math.max(offset, 0);
		Map<String, Object> map = new HashMap<>();
		map.put("pageNo", offset);
		map.put("pageSize", offset);
		map.put("wechat", wechat);
		map.put("qq", qq);
		map.put("sname", sname);

		List<Student> list = studentDao.fuzzySarch(map);
		MyBatisUtil.close(session);
		for (Student s : list) {
			System.out.print(s);
		}
	}

	// 按条件模糊分页查询2
	public void fuzzySarch(int pageNo, int pageSize) {

		SqlSession session = MyBatisUtil.getSqlSession();
		StudentDao studentDao = session.getMapper(StudentDao.class);
		int offset = (pageNo - 1) * pageSize;
		offset = Math.max(offset, 0);
		Map<String, Object> map = new HashMap<>();
		map.put("pageNo", offset);
		map.put("pageSize", offset);

		List<Student> list = studentDao.fuzzySarch(map);
		MyBatisUtil.close(session);
		for (Student s : list) {
			System.out.print(s);
		}
	}

	// 按条件模糊分页查询3
	public void fuzzySarch(int pageNo, int pageSize, String sname) {

		SqlSession session = MyBatisUtil.getSqlSession();
		StudentDao studentDao = session.getMapper(StudentDao.class);
		int offset = (pageNo - 1) * pageSize;
		offset = Math.max(offset, 0);
		Map<String, Object> map = new HashMap<>();
		map.put("pageNo", offset);
		map.put("pageSize", offset);
		map.put("sname", sname);

		List<Student> list = studentDao.fuzzySarch(map);
		MyBatisUtil.close(session);
		for (Student s : list) {
			System.out.print(s);
		}

	}

	// 完善学生的信息查询：按照年级编号，姓名、手机号、身份证号，以及入学时间（按照区间）
	public void fuzzySearchInfo(String sname, int gradeid, String telephone, Date dateStart, Date dateEnd) {

		SqlSession session = MyBatisUtil.getSqlSession();
		StudentDao studentDao = session.getMapper(StudentDao.class);
		Map<String, Object> map = new HashMap<>();
		map.put("sname", sname);
		map.put("gradeid", gradeid);
		map.put("telephone", telephone);
		map.put("dateStart", dateStart);
		map.put("dateEnd", dateEnd);

		List<Student> list = studentDao.fuzzySearchInfo(map);
		int num = studentDao.fuzzySearchCount(map);
		MyBatisUtil.close(session);
		for (Student s : list) {
			System.out.print(s);
		}
		System.out.println("此条件下共有" + num + "人");
	}

	// 修改
	public void update(Student s) {
		SqlSession session = MyBatisUtil.getSqlSession();
		StudentDao studentDao = session.getMapper(StudentDao.class);
		// int result = studentDao.update();
		session.commit();
		// System.out.println(result + "条修改成功！");
		MyBatisUtil.close(session);
	}

	// 修改2
	public void update2(Student s) {
		SqlSession session = MyBatisUtil.getSqlSession();
		StudentDao studentDao = session.getMapper(StudentDao.class);
		Map<String, Object> map = new HashMap<>();
		int result = studentDao.update(map);
		session.commit();
		System.out.println(result + "条修改成功！");
		MyBatisUtil.close(session);
	}

	// 删除
	public void del(int num) {
		SqlSession session = MyBatisUtil.getSqlSession();
		StudentDao studentDao = session.getMapper(StudentDao.class);
		int result = studentDao.del(num);
		session.commit();
		System.out.println(result + "条删除成功！");
		MyBatisUtil.close(session);

	}

	// 增加
	public void add(Student s) {
		SqlSession session = MyBatisUtil.getSqlSession();
		StudentDao studentDao = session.getMapper(StudentDao.class);
		int result = studentDao.add(s);
		session.commit();
		System.out.println(result + "条增加成功！");
		MyBatisUtil.close(session);

	}

	public static void main(String[] args) {
		TestStudent tg = new TestStudent();
		// 条件查询
		// tg.searchStu();

		// 条件查询人数
		// tg.searchStuCount();

		// 查询全部
		// tg.searchAll();

		// 查询数量
		// tg.searchCount();

		// 查询单个
		// tg.getOne(145);

		// 增加
//		tg.add(new Student("李狗蛋1", "男", "1234567", "233333", "ligoudan1", 3, "2020-07-04", "巢湖", "2020-07-05", "合肥包河"));

		// 修改
		// tg.update(new Student(227, "李狗蛋-1", "男", "1234567227", "233333227",
		// "ligoudan1227", 3, "2020-07-04", "巢湖",
		// "2020-07-05", "合肥包河"));

		// 删除
		// tg.del(226);

		// 分页查询
		// tg.searchLimit(2, 5);

		// 查询单个+课程名
		// tg.searchGrade(1);

		// 按条件模糊分页查询
		// tg.fuzzySarch(2, 5, "w", "3", "三");
		// tg.fuzzySarch(2, 5);
		// tg.fuzzySarch(2, 5, "李");

		// 完善学生的信息查询：按照年级编号，姓名、手机号、身份证号，以及入学时间（按照区间）
		tg.fuzzySearchInfo("李", 2, "2", new Date(100, 6, 1), new Date(120, 7, 10));

	}

}
